package com.htht.smartschool.service.exam;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.htht.smartschool.common.service.BaseService;
import com.htht.smartschool.model.bigexam.BigExamBean;
import com.htht.smartschool.model.bigexam.BigExamCourse;
import com.htht.smartschool.model.bigexam.CourseBean;
import com.htht.smartschool.model.bigexam.ExamResult;
import com.htht.smartschool.model.bigexam.UserStudent;
import com.htht.smartschool.model.department.TDepartment;

public class UpdateResultService extends BaseService {
	/**
	 * 查询校级考试列表
	 * @param bigExamBean
	 * @return
	 */
	public List<BigExamBean> getExamsByType(BigExamBean bigExamBean) {
		String sql1="SELECT EXAMCODE,EXAMNAME,EXAMTYPE FROM t_big_exam "+
				" where EXAMCODE IN (SELECT T.EXAMCODE FROM t_big_exam_classes T ";
		String sql2="SELECT COUNT(*) FROM t_big_exam "+
		" where EXAMCODE IN (SELECT T.EXAMCODE FROM t_big_exam_classes T ";
		if(bigExamBean.getClasscode()!=null&&!"".equals(bigExamBean.getClasscode())){
			sql1+="WHERE T.CLASSCODE='"+bigExamBean.getClasscode()+"'";
			sql2+="WHERE T.CLASSCODE='"+bigExamBean.getClasscode()+"'";
		}else{
			sql1+="WHERE T.CLASSCODE IN (SELECT classcode FROM t_data_role WHERE rolecode = '"+bigExamBean.getRolecode()+"')";
			sql2+="WHERE T.CLASSCODE IN (SELECT classcode FROM t_data_role WHERE rolecode = '"+bigExamBean.getRolecode()+"')";
		}
		sql1+=")";
		sql2+=")";
		//System.out.println(sql1);
		List<Object[]> list = getDao().queryBySqlPage(sql1, bigExamBean.getCurrentpage(), bigExamBean.getPagesize());
		int count = getDao().getCountBySqlPage(sql2);
		List<BigExamBean> beanList = new ArrayList<BigExamBean>();
		for(int i = 0; i < list.size(); i++){
			Object[] obj = list.get(i);
			BigExamBean eb = new BigExamBean();
			eb.setExamcode((String.valueOf(obj[0])));
			eb.setExamname(String.valueOf(obj[1]));
			eb.setExamtype(String.valueOf(obj[2]));
			eb.setClasscode(bigExamBean.getClasscode());
			eb.setTotal((long) count);
			beanList.add(eb);
		}
		return beanList;
	}
	
	/**
	 *查询一批考试对应的科目
	 * @param bigExamBean
	 * @return
	 */
	public List<BigExamCourse> getBigExamCourses(BigExamBean bigExamBean){
		String sql1="SELECT EXAMCODE,COURSECODE,COURSENAME FROM t_big_exam_course WHERE EXAMCODE = '"+bigExamBean.getExamcode()+"'";
		String sql2="SELECT COUNT(*) FROM t_big_exam_course WHERE EXAMCODE = '"+bigExamBean.getExamcode()+"'";
		List<Object[]> list = getDao().queryBySqlPage(sql1, bigExamBean.getCurrentpage(), bigExamBean.getPagesize());
		int count = getDao().getCountBySqlPage(sql2);
		List<BigExamCourse> beanList = new ArrayList<BigExamCourse>();
		for(int i = 0; i < list.size(); i++){
			Object[] obj = list.get(i);
			BigExamCourse ec = new BigExamCourse();
			ec.setExamCode((String.valueOf(obj[0])));
			ec.setCourseCode((String.valueOf(obj[1])));
			ec.setCourseName((String.valueOf(obj[2])));
			ec.setClassCode(String.valueOf(bigExamBean.getClasscode()));
			ec.setTotal((long) count);
			beanList.add(ec);
		}
		return beanList;
	}
	/**
	 * 查询学生成绩列表
	 * @param bigExamCourse
	 * @return
	 */
	public List<ExamResult> getScoreList(BigExamCourse bigExamCourse){
		String sql1="SELECT ID,STUDENTCODE,STUDENTNAME,SCORENUMBER FROM t_result WHERE EXAMCODE='"+bigExamCourse.getExamCode()+
				"' AND COURSECODE='"+bigExamCourse.getCourseCode()+"'";
		String sql2="SELECT COUNT(*) FROM t_result WHERE EXAMCODE='"+bigExamCourse.getExamCode()+
				"' AND COURSECODE='"+bigExamCourse.getCourseCode()+"'";
		String sql3="SELECT DISTINCT SCORENUMBER FROM t_result where examcode='"+bigExamCourse.getExamCode()+
				"' AND COURSECODE='"+bigExamCourse.getCourseCode()+"'";
		if(bigExamCourse.getClassCode()!=null&&!"".equals(bigExamCourse.getClassCode())){
			sql1+=" AND CLASSCODE='"+bigExamCourse.getClassCode()+"'";
			sql2+=" AND CLASSCODE='"+bigExamCourse.getClassCode()+"'";
			sql3+=" AND CLASSCODE='"+bigExamCourse.getClassCode()+"'";
		}
		sql1+=" ORDER BY CAST(SCORENUMBER AS SIGNED) DESC";
		//sql3+=" ORDER BY CAST(SCORENUMBER AS SIGNED) DESC";
		List<Object[]> list = getDao().queryBySqlPage(sql1, bigExamCourse.getCurrentpage(), bigExamCourse.getPagesize());
		int count = getDao().getCountBySqlPage(sql2);
		//List<Object> list3= getDao().queryBySQL(sql3);
		List<ExamResult> beanList = new ArrayList<ExamResult>();
		for(int i = 0; i < list.size(); i++){
			Object[] obj = list.get(i);
			ExamResult er = new ExamResult();
			er.setId(Long.parseLong(obj[0].toString()));
			er.setStudentCode(String.valueOf(obj[1]));
			er.setStudentName(String.valueOf(obj[2]));
			er.setScoreNumber(String.valueOf(obj[3]));
			er.setScoreIndex(String.valueOf(i+1));
//			for(int j=0;j<list3.size();j++){
//				String score = String.valueOf(list3.get(j));
//				if(score.equals(er.getScoreNumber())){
//					er.setScoreIndex(String.valueOf(j+1));
//					break;
//				}
//			}
			er.setTotal((long) count);
			beanList.add(er);
		}
		return beanList;
	}
	
	/**
	 * 修改学生成绩
	 * @param examResult
	 * @return
	 */
	public boolean updateScore(ExamResult examResult) {
		String sql ="UPDATE t_result SET SCORENUMBER = '"+examResult.getScoreNumber()+"' WHERE ID='"+examResult.getId()+"'";
		int res = getDao().executeSQL(sql,examResult);
		if(res == -1){
			return false;
		}else{
			return true;
		}
	}
	/**
	 * 获得校总榜
	 * @param bigExamBean
	 * @return
	 */
	public List<ExamResult> getTotalScoreList(BigExamBean bigExamBean){
		StringBuilder builder1=new StringBuilder();
		StringBuilder builder2=new StringBuilder();
		builder1.append("SELECT *,SUM(scorenumber) from t_result where examcode='"+bigExamBean.getExamcode()+"'");
		builder2.append("SELECT count(t.counts) FROM(SELECT DISTINCT(studentcode),count(*) counts FROM t_result WHERE"
			+" examcode = '"+bigExamBean.getExamcode()+"'");
		if(bigExamBean.getClasscode()!=null&&!"".equals(bigExamBean.getClasscode())&&!"null".equals(bigExamBean.getClasscode())){
			//builder1.append(" and classcode='"+bigExamBean.getClasscode()+"'");
			builder2.append(" and classcode='"+bigExamBean.getClasscode()+"'");
		}
		builder1.append(" GROUP by studentcode order by	CAST(SUM(scorenumber) AS SIGNED) DESC");
		builder2.append(" GROUP BY studentcode) t");
		//String sql3="SELECT DISTINCT SUM(scorenumber) from t_result where examcode='"+bigExamBean.getExamcode()+"' GROUP BY studentcode ORDER BY CAST(SUM(scorenumber) AS SIGNED) desc";
		List<Object[]> list = getDao().queryBySqlPage(builder1.toString(), bigExamBean.getCurrentpage(), bigExamBean.getPagesize());
		int  count = getDao().getCountBySqlPage(builder2.toString());
		//List<Object> list3= getDao().queryBySQL(sql3);
		List<ExamResult> beanList = new ArrayList<ExamResult>();
		for(int i = 0; i < list.size(); i++){
			Object[] obj = list.get(i);
			if(bigExamBean.getClasscode()==null||String.valueOf(obj[9]).equals(bigExamBean.getClasscode())){
			ExamResult er = new ExamResult();
			er.setStudentCode(String.valueOf(obj[4]));
			er.setStudentName(String.valueOf(obj[5]));
			er.setScoreNumber(String.valueOf(obj[14]));
			er.setScoreIndex(String.valueOf(i+1));
			er.setExamCode(bigExamBean.getExamcode());
//			for(int j=0;j<list3.size();j++){
//				String score = String.valueOf(list3.get(j));
//				if(score.equals(er.getScoreNumber())){
//					er.setScoreIndex(String.valueOf(j+1));
//					break;
//				}
//			}
			er.setTotal((long) count);
			beanList.add(er);
			}
		}
		return beanList;
	}
	
	/**
	 * 获取一个学生的各科目成绩
	 * @param examResult
	 * @return
	 */
	public List<ExamResult> getOneScore(ExamResult examResult){
		String sql1="SELECT id,coursename,scorenumber,studentname from t_result where studentcode='"+examResult.getStudentCode()+
				"' and examcode='"+examResult.getExamCode()+"'";
		String sql2="SELECT count(*) from t_result where studentcode='"+examResult.getStudentCode()+
				"' and examcode='"+examResult.getExamCode()+"'";
		List<Object[]> list = getDao().queryBySqlPage(sql1.toString(), examResult.getCurrentpage(), examResult.getPagesize());
		int count = getDao().getCountBySqlPage(sql2.toString());
		List<ExamResult> beanList = new ArrayList<ExamResult>();
		for(int i = 0; i < list.size(); i++){
			Object[] obj = list.get(i);
			ExamResult er = new ExamResult();
			er.setId(Long.parseLong(obj[0].toString()));
			er.setCourseName(String.valueOf(obj[1]));
			er.setScoreNumber(String.valueOf(obj[2]));
			er.setStudentName(String.valueOf(obj[3]));
			er.setTotal((long) count);
			beanList.add(er);
		}
		return beanList;
	}
	
	/**
	 * 获取小测科目
	 * @param bigExamBean
	 * @return
	 */
	public List<BigExamBean> getExamsClass(BigExamBean bigExamBean){
		String sql1="select examcode,remark from t_exam";
		String sql2="SELECT count(*) from t_exam";
		if(bigExamBean.getClasscode()!=null&&!"".equals(bigExamBean.getClasscode())){
			sql1+=" where examcode in (SELECT examcode from t_examinationresult where classcode ='"+bigExamBean.getClasscode()+"')";
			sql2+=" where examcode in (SELECT examcode from t_examinationresult where classcode ='"+bigExamBean.getClasscode()+"')";
		}else{
			sql1+=" where examcode in (SELECT examcode from t_examinationresult where classcode in "+
				"(SELECT classcode FROM t_data_role WHERE rolecode = '"+bigExamBean.getRolecode()+"'))";
			sql2+=" where examcode in (SELECT examcode from t_examinationresult where classcode in "+
					"(SELECT classcode FROM t_data_role WHERE rolecode = '"+bigExamBean.getRolecode()+"'))";
			
		}
		List<Object[]> list = getDao().queryBySqlPage(sql1.toString(), bigExamBean.getCurrentpage(), bigExamBean.getPagesize());
		int count = getDao().getCountBySqlPage(sql2.toString());
		List<BigExamBean> beanList = new ArrayList<BigExamBean>();
		for(int i = 0; i < list.size(); i++){
			Object[] obj = list.get(i);
			BigExamBean be = new BigExamBean();
			be.setExamcode(String.valueOf(obj[0]));
			be.setExamname(String.valueOf(obj[1]));
			be.setTotal((long) count);
			beanList.add(be);
		}
		return beanList;
	}
	/**
	 * 获取小测排名列表
	 * @param bigExamBean
	 * @return
	 */
	public List<ExamResult> getResultList(BigExamBean bigExamBean){
		String sql1="SELECT t.id,s.name,t.result from t_examinationresult t left join t_student s"+
				" on t.studentcode=s.studentcode where examcode='"+bigExamBean.getExamcode()+"' ORDER BY CAST( t.result as SIGNED) desc";
		String sql2="SELECT count(*) from t_examinationresult t left join t_student s"+
				" on t.studentcode=s.studentcode where examcode='"+bigExamBean.getExamcode()+"'";
		//String sql3="SELECT DISTINCT result FROM t_examinationresult WHERE "+
		//		"examcode = '"+bigExamBean.getExamcode()+"' ORDER BY CAST(result AS SIGNED) DESC";
		List<Object[]> list = getDao().queryBySqlPage(sql1.toString(), bigExamBean.getCurrentpage(), bigExamBean.getPagesize());
		int count = getDao().getCountBySqlPage(sql2.toString());
		//List<Object[]> list3 = getDao().queryBySQL(sql3.toString());
		List<ExamResult> beanList = new ArrayList<ExamResult>();
		for(int i = 0; i < list.size(); i++){
			Object[] obj = list.get(i);
			ExamResult be = new ExamResult();
			be.setId(Long.parseLong(String.valueOf(obj[0])));
			be.setStudentName(String.valueOf(obj[1]));
			be.setScoreNumber(String.valueOf(obj[2]));
			be.setScoreIndex(String.valueOf(i+1));
//			for(int j=0;j<list3.size();j++){
//				String score = String.valueOf(list3.get(j));
//				if(score.equals(be.getScoreNumber())){
//					be.setScoreIndex(String.valueOf(j+1));
//					break;
//				}
//			}
			be.setTotal((long) count);
			beanList.add(be);
		}
		return beanList;
		
	}
	/**
	 * 修改小测成绩
	 * @param bigExamBean
	 * @return
	 */
	public boolean updateExamScore(ExamResult ExamResult){
		String sql1="UPDATE t_examinationresult set result='"+ExamResult.getScoreNumber()+"' where id='"+ExamResult.getId()+"'";
		int res = getDao().executeSQL(sql1.toString(),ExamResult);
		if(res==-1){
			return false;
		}
		return true;
	}
	
	/**
	 * 家长获取孩子的考试列表
	 * @param userStudent
	 * @return
	 */
	public List<BigExamBean> getChildsExams(UserStudent userStudent){
		String sql1="SELECT * FROM t_big_exam b LEFT JOIN (SELECT DISTINCT"+
				" examcode as code FROM t_result t "+
	"LEFT JOIN t_user_student s ON t.studentcode = s.studentcode "+
	"WHERE s.usercode = '"+userStudent.getUsercode()+"'";
		String sql2="SELECT count(*) FROM t_big_exam b LEFT JOIN (SELECT DISTINCT"+
				" examcode as code FROM t_result t "+
	"LEFT JOIN t_user_student s ON t.studentcode = s.studentcode "+
	"WHERE s.usercode = '"+userStudent.getUsercode()+"'";
	if(userStudent.getStudentcode()!=null&&!"".equals(userStudent.getStudentcode())){
		sql1+=" AND s.studentcode = '"+userStudent.getStudentcode()+"'";
		sql2+=" AND s.studentcode = '"+userStudent.getStudentcode()+"'";
	}
		sql1+=") AS m ON m.code = b.examcode ";
		sql2+=") AS m ON m.code = b.examcode ";
		List<Object[]> list = getDao().queryBySqlPage(sql1.toString(), userStudent.getCurrentpage(), userStudent.getPagesize());
		int count = getDao().getCountBySqlPage(sql2.toString());
		List<BigExamBean> beanList = new ArrayList<BigExamBean>();
		for(int i = 0; i < list.size(); i++){
			Object[] obj = list.get(i);
			BigExamBean be = new BigExamBean();
			be.setExamcode(String.valueOf(obj[1]));
			be.setExamname(String.valueOf(obj[2]));
			be.setTotal((long) count);
			be.setStudentcode(userStudent.getStudentcode());
			beanList.add(be);
		}
		return beanList;
	}
	
	/**
	 * 查询孩子一次考试的各科成绩、总分、名次
	 * @param bigExamBean
	 * @return
	 */
	public List<ExamResult> getChildScore(BigExamBean bigExamBean){
		String sql1="SELECT r.coursename,r.scorenumber FROM t_result r WHERE	r.examcode = '"+bigExamBean.getExamcode()+
				"' AND r.studentcode = '"+bigExamBean.getStudentcode()+"'";
		String sql2="SELECT count(*) FROM t_result r WHERE	r.examcode = '"+bigExamBean.getExamcode()+
				"' AND r.studentcode = '"+bigExamBean.getStudentcode()+"'";
		List<Object[]> list = getDao().queryBySqlPage(sql1.toString(), bigExamBean.getCurrentpage(), bigExamBean.getPagesize());
		int count = getDao().getCountBySqlPage(sql2.toString());
		List<ExamResult> beanList = new ArrayList<ExamResult>();
		for(int i = 0; i < list.size(); i++){
			Object[] obj = list.get(i);
			ExamResult be = new ExamResult();
			be.setCourseName(String.valueOf(obj[0]));
			be.setScoreNumber(String.valueOf(obj[1]));
			be.setTotal((long) count);
			beanList.add(be);
		}
		String sql3="SELECT studentcode,SUM(scorenumber) FROM t_result WHERE examcode = '"+bigExamBean.getExamcode()+"' GROUP BY "+
				"studentcode ORDER BY CAST(SUM(scorenumber) AS SIGNED) DESC";
//		String sql4="SELECT DISTINCT sum(scorenumber) FROM t_result WHERE examcode = '"+bigExamBean.getExamcode()+"' GROUP BY "
//				+ "studentcode ORDER BY CAST(SUM(scorenumber) AS SIGNED) DESC";//防止成绩重复产生并列

		List<Object[]> list2 = getDao().queryBySQL(sql3.toString());
		//List<Object[]> list3 = getDao().queryBySQL(sql4.toString());
		for(int i = 0; i < list2.size(); i++){
			Object[] obj = list2.get(i);
			if(String.valueOf(obj[0]).equals(bigExamBean.getStudentcode())){
				ExamResult be = new ExamResult();
				be.setCourseName("总分");
				be.setScoreNumber(String.valueOf(obj[1]));
				be.setScoreIndex(String.valueOf(i+1));
//				for(int j=0;j<list3.size();j++){
//					String score = String.valueOf(list3.get(j));
//					if(score.equals(be.getScoreNumber())){
//						be.setScoreIndex(String.valueOf(j+1));
//						break; 
//					}
//				}
				beanList.add(be);
			}
		}
		return beanList;
	}
	/**
	 * 家长查询小测验考试列表
	 * @param userStudent
	 * @return
	 */
	public List<BigExamBean> getChileTests(UserStudent userStudent){
		String sql1="SELECT e.examcode,e.studentcode,ex.remark FROM t_examinationresult e "+
				"LEFT JOIN t_user_student s ON e.studentcode = s.studentcode "+
				"LEFT JOIN t_exam ex ON e.examcode = ex.examcode "+
				"WHERE s.usercode = '"+userStudent.getUsercode()+"' and s.studentcode='"+userStudent.getStudentcode()+"' ";
		String sql2="SELECT count(*) FROM t_examinationresult e "+
				"LEFT JOIN t_user_student s ON e.studentcode = s.studentcode "+
				"LEFT JOIN t_exam ex ON e.examcode = ex.examcode "+
				"WHERE s.usercode = '"+userStudent.getUsercode()+"' and s.studentcode='"+userStudent.getStudentcode()+"' ";
		List<Object[]> list = getDao().queryBySqlPage(sql1.toString(), userStudent.getCurrentpage(), userStudent.getPagesize());
		int count = getDao().getCountBySqlPage(sql2.toString());
		List<BigExamBean> beanList = new ArrayList<BigExamBean>();
		for(int i = 0; i < list.size(); i++){
			Object[] obj = list.get(i);
			BigExamBean be = new BigExamBean();
			be.setExamcode(String.valueOf(obj[0]));
			be.setStudentcode(String.valueOf(obj[1]));
			be.setExamname(String.valueOf(obj[2]));
			be.setTotal((long) count);
			beanList.add(be);
		}
		return beanList;
	}
	/**
	 * 家长查询孩子一次测验的成绩和排名
	 * @param bigExamBean
	 * @return
	 */
	public ExamResult getChildTestScore(BigExamBean bigExamBean){
		String sql="SELECT studentcode,result from t_examinationresult where examcode='"+bigExamBean.getExamcode()+"' ORDER BY CAST(result AS SIGNED) desc";
		List<Object[]> list = getDao().queryBySQL(sql.toString());
		ExamResult be = new ExamResult();
		for(int i = 0; i < list.size(); i++){
			Object[] obj = list.get(i);
			if(String.valueOf(obj[0]).equals(bigExamBean.getStudentcode())){
			be.setStudentCode(String.valueOf(obj[0]));
			be.setScoreNumber(String.valueOf(obj[1]));
			be.setScoreIndex(String.valueOf(i+1));
			}
		}
//		String sql2="SELECT DISTINCT result from t_examinationresult where examcode='"+bigExamBean.getExamcode()+"' ORDER BY CAST(result AS SIGNED) DESC";
//		List<Object[]> scoreList = getDao().queryBySQL(sql2.toString());
//		for(int i = 0; i < scoreList.size(); i++){
//			Object obj = scoreList.get(i);
//			if(String.valueOf(obj).equals(be.getScoreNumber())){
//			be.setScoreIndex(String.valueOf(i+1));
//			break;
//			}
//		}
		return be;
	}
	
}
